package com.my.java_web.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
	
	private static String url = "jdbc:mysql://127.0.0.1:3306/java-web";
	private static String username = "root";
	private static String pw = "123456";
	
	//静态代码快，只会在类加载时只执行一次
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//获取链接
	public static Connection getConnection() {
		Connection conn = null;
//		Statement statement = null;		
		try {
			conn = DriverManager.getConnection(url, username, pw);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	//释放链接
	public static void release(Connection conn, Statement statement) {
		try {
			statement.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	//执行增加修改操作（带参数）
	public static void execute(String sql) {		
		Connection conn = null;
		Statement statement = null;
//		String names = "";
//		String values = "";
//		names = names + "account, " + "name, " + "password";
//		values = values + user.getAccount() + ", " + user.getName() + ", " +user.getPassword();
//		String sql = "insert into user (" + names + ") values (" + values + ")";
		
		try {
//			conn = DriverManager.getConnection(url, username, pw);
			conn = getConnection();
			statement = conn.createStatement();			
			statement.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			release(conn, statement);		
		}
	}
	
	//执行带返回数据的sql(查询sql)
	public static List<Map<String, String>> executeQuery(String sql){
		List<Map<String, String>> results = new ArrayList<>();
		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
//			conn = DriverManager.getConnection(url, username, pw);
			conn = getConnection();
			//防止sql注入
			//select count(1) from user where account = ? and password = ?
//			preStatement = conn.prepareStatement(sql);
//			preStatement.setString(1, "admin");
//			preStatement.setString(2, "123456");
			statement = conn.createStatement();			
//			statement.execute(sql);
			resultSet =  statement.executeQuery(sql);			
			//结果集对应表的元数据信息
			ResultSetMetaData meteData = resultSet.getMetaData();
			//返回表中一共有多少列
			int count = meteData.getColumnCount();
//			遍历表中每一行的数据，
//			int index = 1;
			while(resultSet.next()) {
				//保存每一列的map对象
				Map<String, String> tempMap = new HashMap<>();
//				遍历每行中的每一列数据
				for(int i = 1; i<= count ; i++) {
//					System.out.println(
//			meteData.getColumnLabel(i) +":" + resultSet.getString(i));
					tempMap.put(meteData.getColumnLabel(i), resultSet.getString(i));
				}
//				System.out.println("results[" + index + "]: " + resultSet);
//				index = index + 1;
				results.add(tempMap);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			release(conn, statement);		
		}
//		Map<String, Object> map= new HashMap<>();
		return results;
	}

}
